package main

import (
	"database/sql"
	"fmt"
	"github.com/jinzhu/gorm"
	_ "github.com/jinzhu/gorm/dialects/mysql"
)

// 定义模型
type User struct {
	gorm.Model
	Name string
	Age  int64
}
type Emails struct {
	gorm.Model
	Email sql.NullString `gorm:"email"`
}

type CreditCard struct {
	ID     int64
	Number string
}

func main() {
	// 使用 GORM 连接到 MySQL 数据库
	db, err := gorm.Open("mysql", "usr1:12345@tcp(114.132.68.147:3306)/db1?charset=utf8mb4&parseTime=True&loc=Local")
	if err != nil {
		panic(err.Error())
	}
	// 关闭数据库连接
	defer db.Close()

	//模型与表进行对应
	db.AutoMigrate(&User{})
	db.AutoMigrate(&Emails{})
	db.AutoMigrate(&CreditCard{})
	//创建记录
	//u1 := User{Name: "张三", Age: 18}
	//u2 := User{Name: "李四", Age: 20}
	//
	//db.Create(&u1)
	//db.Create(&u2)

	//查询记录
	//1.一般查询
	//db.First(&user)
	//db.Take(&user)
	//db.Last(&user)
	//db.Find(&user, 2)
	//db.Where("name = ?", "李四").First(&user)
	//db.Where("name = ?", "李四").Find(&user)
	//db.Where("name <> ?", "李四").Find(&user)
	//db.Where("name IN (?)", []string{"李四", "李四 2"}).Find(&user)
	//db.Where("name LIKE ?", "%李四%").Find(&user)
	//db.Where("name = ? AND age <= ?", "李四", "22").Find(&user)
	//lastWeek := time.Now().Add(-7 * 24 * time.Hour)
	//today := time.Now()
	////db.Where("updated_at > ?", lastWeek).Find(&user)
	//db.Where("created_at BETWEEN ? AND ?", lastWeek, today).Find(&user)
	//db.Where(&User{Name: "李四", Age: 20}).First(&user)
	//db.Where(map[string]interface{}{"name": "李四", "age": 20}).Find(&user)
	//db.Where([]int64{1, 2}).Find(&user)
	//db.Not("name", "李四").First(&user)
	//db.Not("name", []string{"李四", "李四 2"}).Find(&user)
	//db.Not([]int64{1, 2}).First(&user)
	//db.Not([]int64{}).First(&user)
	//db.Not("name = ?", "李四").First(&user)
	//db.Not(User{Name: "李四"}).First(&user)
	//db.Where("age = ?", "18").Or("age = ?", "19").Find(&user)
	//db.Where("name = '张三'").Or(User{Name: "张三 2"}).Find(&user)
	//db.Where("name = '张三'").Or(map[string]interface{}{"name": "张三 2"}).Find(&user)
	//db.First(&user, "id = ?", "1")
	//db.Find(&user, "name = ?", "张三")
	//db.Find(&user, "name <> ? AND age > ?", "张三", 20)
	//db.Find(&user, User{Age: 20})
	//db.Find(&user, map[string]interface{}{"age": 20})
	//db.Set("gorm:query_option", "FOR UPDATE").First(&user, 1)
	//db.FirstOrInit(&user, User{Name: "王五"})
	//db.FirstOrInit(&user, map[string]interface{}{"name": "张三"})
	//db.Where(User{Name: "王五"}).Attrs(User{Age: 100}).FirstOrInit(&user)
	//db.Where(User{Name: "张三"}).Attrs(User{Age: 18}).FirstOrInit(&user)
	//db.Where(User{Name: "王五"}).Assign(User{Age: 100}).FirstOrInit(&user)
	//db.Where(User{Name: "张三"}).Attrs(User{Age: 100}).FirstOrInit(&user)
	//db.Where(User{Name: "王五"}).Assign(User{Age: 20}).FirstOrInit(&user)
	//db.Where(User{Name: "张三"}).Assign(User{Age: 100}).FirstOrInit(&user)
	//db.FirstOrCreate(&user, User{Name: "王五"})
	//db.Where(User{Name: "赵6"}).Attrs(User{Age: 20}).FirstOrCreate(&user)
	//db.Where(User{Name: "王五"}).Attrs(User{Age: 0}).FirstOrCreate(&user)
	//db.Where(User{Name: "田七"}).Assign(User{Age: 20}).FirstOrCreate(&user)
	//db.Where(User{Name: "王五"}).Assign(User{Age: 10}).FirstOrCreate(&user)
	//db.Where("age > ?", db.Table("users").Select("AVG(age)").Where("name = ?", "')));").SubQuery()).Find(&user)
	//db.Where("age > ?", db.Table("users").Select("AVG(age)").Where("name LIKE ?", "%").SubQuery()).Find(&user)
	//db.Select("name, age").Find(&user)
	//db.Select([]string{"name", "age"}).Find(&user)
	//a, _ := db.Table("users").Select("COALESCE(age,?)", 20).Rows()
	////fmt.Println(user)
	//for a.Next() {
	//	var b int64
	//	err = a.Scan(&b)
	//	if err != nil {
	//		panic(err)
	//	}
	//	fmt.Println(b)
	//}
	//var user []User // 声明模型结构体类型变量
	//db.Order("age desc, name").Find(&user)
	//db.Order("age desc").Order("name").Find(&user)
	//var user1, user2 []User
	//db.Order("age desc").Find(&user1).Order("age", true).Find(&user2)
	//
	//db.Limit(3).Find(&user)
	//db.Limit(10).Find(&user).Limit(-1).Find(&user)
	//db.Order("age desc").Find(&user).Order("age", true).Find(&user)
	//db.Offset(3).Limit(10).Find(&user)
	//db.Offset(10).Find(&user).Offset(-1).Find(&user)
	//db.Create(&User{Name: "张三", Age: 1000})
	//var count int64
	//////db.Where("name = ?", "张三").Or("name = ?", "张三 2").Find(&user).Count(&count)
	//////db.Model(&User{}).Where("name = ?", "张三").Count(&count)
	//////db.Table("users").Count(&count)
	//db.Table("users").Select("count(distinct(name))").Count(&count)
	//fmt.Println(count)
	//rows, erro := db.Table("users").Select("date(created_at) as date, sum(age) as total").Group("date(created_at)").Rows()
	//if erro != nil {
	//	// 处理错误
	//	panic(erro)
	//}
	//for rows.Next() {
	//	var date string
	//	var total float64
	//
	//	err = rows.Scan(&date, &total)
	//	if err != nil {
	//		// 处理错误
	//		panic(err)
	//	}
	//
	//	// 处理每一行的数据
	//	fmt.Printf("Date: %s, Total: %f\n", date, total)
	//}
	// 使用Scan将多条结果扫描进事先准备好的结构体切片中
	//type Result struct {
	//	Date  time.Time
	//	Total int
	//}
	//var rets []Result
	//db.Table("users").Select("date(created_at) as date, sum(age) as total").Group("date(created_at)").Scan(&rets)
	//
	//for _, ret := range rets {
	//	fmt.Println(ret.Total, "-----", ret.Date)
	//}
	//rows, erro := db.Table("users").Select("date(created_at) as date, sum(age) as total").Group("date(created_at)").Having("sum(age) > ?", 20).Rows()
	//if erro != nil {
	//	panic(erro)
	//}
	//for rows.Next() {
	//	var Date string
	//	var Total int64
	//	err = rows.Scan(&Date, &Total)
	//	if err != nil {
	//		panic(err)
	//	}
	//	fmt.Println(Date)
	//	fmt.Println("------------------------------")
	//	fmt.Println(Total)
	//}

	//type Result struct {
	//	Date  time.Time
	//	Total int64
	//}
	//var results []Result
	//db.Table("users").Select("date(created_at) as date, sum(age) as total").Group("date(created_at)").Having("sum(age) > ?", 20).Scan(&results)
	//fmt.Println(results)
	//e1 := Emails{Email: sql.NullString{String: "12345@163.com", Valid: true}}
	//e2 := Emails{Email: sql.NullString{String: "789@qq.com", Valid: true}}
	//e3 := Emails{Email: sql.NullString{String: "10jqk@xxx.com", Valid: true}}
	//db.Create(&e1)
	//db.Create(&e2)
	//db.Create(&e3)
	//e4 := Emails{Email: sql.NullString{String: "abc@163.com", Valid: true}}
	//e5 := Emails{Email: sql.NullString{String: "efg@qq.com", Valid: true}}
	//db.Create(&e4)
	//db.Create(&e5)
	//e6 := Emails{Email: sql.NullString{String: "xxx@xxx.com", Valid: true}}
	//db.Create(&e6)
	//rows, erro := db.Table("users").Select("users.name, emails.email").Joins("left join emails on emails.id = users.id").Rows()
	//type Result struct {
	//	Name  string
	//	Email string
	//}
	//var results []Result
	//db.Table("users").Select("users.name, emails.email").Joins("left join emails on emails.id = users.id").Scan(&results)
	//for _, ret := range results {
	//	fmt.Println(ret.Name, "-----", ret.Email)
	//}
	//c1 := CreditCard{ID: 2, Number: "123456"}
	//c2 := CreditCard{ID: 777, Number: "88888888"}
	//c3 := CreditCard{ID: 888, Number: "abcde"}
	//db.Create(&c1)
	//db.Create(&c2)
	//db.Create(&c3)
	//var user []User
	//db.Joins("JOIN emails ON emails.id = users.id AND emails.email = ?", "789@qq.com").Joins("JOIN credit_cards ON credit_cards.id = users.id").Where("credit_cards.number = ?", "123456").Find(&user)
	//fmt.Println(user)
	//var ages []int64
	//db.Find(&user).Pluck("age", &ages)
	//fmt.Println(ages)
	//var names []string
	////db.Model(&User{}).Pluck("name", &names)
	//db.Table("users").Pluck("name", &names)
	//fmt.Println(names)
	//type Result struct {
	//	Name string
	//	Age  int
	//}
	//var result []Result
	////db.Table("users").Select("name, age").Where("name = ?", "张三").Scan(&result)
	//db.Table("users").Select("name, age").Where("id > ?", 4).Scan(&result)
	//fmt.Println(result)
	// 创建一个查询
	var users []User
	tx := db.Where("name = ?", "张三")
	someCondition := true
	// 添加更多条件
	if someCondition {
		tx = tx.Where("age = ?", 18)
	} else {
		tx = tx.Where("age = ?", 30)
	}
	yetAnotherCondition := true
	if yetAnotherCondition {
		tx = tx.Where("id = ?", 1)
	}
	err = tx.Find(&users).Error
	if err != nil {
		panic(err)
	}
	for _, user := range users {
		fmt.Println(user.ID, user.Name, user.Age)
	}
	fmt.Println("Connected to the database successfully!")
}
